<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>excel操作</title>
</head>
<body>
  <div>
    按列 <input type="text" name="column" id="a" style="width: 80px;" value="567分项"> 将列 <input type="text" name="column"
      id="b" style="width: 80px;" value="唯一编码"> 排序
    <input type="file" id="excel-file" accept=".xlsx, .xls">
  </div>
  <script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
  <script>
    function dom(id) {
      return document.getElementById(id)
    }

    document.getElementById('excel-file').addEventListener('change', function (e) {
      var file = e.target.files[0];
      var reader = new FileReader();
      reader.onload = function (e) {
        var data = new Uint8Array(e.target.result);
        var workbook = XLSX.read(data, { type: 'array' });

        // 假设我们只处理第一个工作表
        var worksheetName = workbook.SheetNames[0];
        var worksheet = workbook.Sheets[worksheetName];
        const json = XLSX.utils.sheet_to_json(worksheet)
        // aoa_to_sheet
        const aCol = dom('a').value
        const bCol = dom('b').value
        const rKey = []
        const lLen = Object.keys(json[0]).findIndex(d => d === aCol)
        Object.keys(json[0]).forEach((key, i, arr) => {
          if (i > lLen) {
            rKey.push(key)
          }
        });
        const list = json.map(d => d[aCol]?.trim()).filter(a => a)
        const arr = []
        function handleData(data) {
          const itm = {}
          rKey.forEach(k => {
            itm[k] = data?.[k]
          })
          return itm
        }

        list.forEach((d, i) => {
          const idx = json.findIndex(j => j?.[bCol] == d)
          if (idx > -1) {
            arr.push(handleData(json[idx]))
            json.splice(idx, 1)
          }
        })
        arr.push(...json.map(handleData))
        const jsonWorkSheet = XLSX.utils.json_to_sheet(arr);

        // 生成新的workbook（如果需要多个工作表）
        var outputWorkbook = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(outputWorkbook, jsonWorkSheet, worksheetName);

        // 将workbook转换为blob对象
        var wbout = XLSX.write(outputWorkbook, { bookType: 'xlsx', type: 'array' });
        var blob = new Blob([wbout], { type: 'application/octet-stream' });


        var link = document.createElement("a");
        link.href = window.URL.createObjectURL(blob);
        link.download = "export.xlsx";

        // 触发下载
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      };
      reader.readAsArrayBuffer(file);
    });
  </script>
</body>
</html>